## Replication Code
## Accountability for the Local Economy at All Levels of Government in United States Elections
## Justin de Benedictis-Kessner Christopher Warshaw
## Run this code 2nd

##----------------##
### Preliminary ####
##----------------##
library(foreign)
library(tidyr)
library(dplyr)
library(haven)
library(stringr)
library(openxlsx)

options(scipen=999)

## Flag for whether we need to redact licensed elections data
include_licensed <- 0

##---------------------------------##
### Import election returns data ####
##---------------------------------##

## 1964-1990 from ICPSR
elections_returns <- read.dta("Election_Returns/election_returns_1964_1990.dta")

## Data for 2016 and 2018 from Stephen Pettigrew, accessed via Harvard Dataverse
## and then compiled into single files
elections_2016 <- read.dta("Election_Returns/election_returns_2016.dta")
elections_2018 <- read.dta("Election_Returns/election_returns_2018.dta")


### Licensed data downloaded from CQ Campaigns and Elections data page
if (include_licensed==0){
	president_elections <- read.csv(file="Election_Returns/potus_public.csv")
}
if (include_licensed==1){
	president_elections <- read.csv(file="Election_Returns/potus.csv")
}
president_elections$year <- substr(president_elections$RaceDate, 1, 4)
president_elections$pres_rep_votes <- as.numeric(as.vector(president_elections$RepVotes))
president_elections$pres_dem_votes <- as.numeric(as.vector(president_elections$DemVotes))
president_elections$pres_dem <- president_elections$pres_dem_votes/(president_elections$pres_dem_votes+president_elections$pres_rep_votes)
president_elections$county_name <- paste(tolower(president_elections$Area),tolower(president_elections$AreaType), sep=" ")
president_elections$county_name <- gsub(" county", "", president_elections$county_name)
president_elections$county_name <- gsub(" city city", " city", president_elections$county_name)
president_elections$county_name <- paste(tolower(president_elections$county_name), sep=" ")
president_elections$county_name <- gsub(" county", "", president_elections$county_name)
president_elections$county_name <- gsub(" parish", "", president_elections$county_name)
president_elections$pres_total_votes <- as.numeric(as.vector(president_elections$TotalVotes))
president_elections <- dplyr::select(president_elections, year, county_name, State, pres_rep_votes, pres_dem_votes, pres_dem,pres_total_votes)
	
if (include_licensed==0){
	governor_elections <- read.csv(file="Election_Returns/governor_public.csv")
}
if (include_licensed==1){
	governor_elections <- read.csv(file="Election_Returns/governor.csv")
}
governor_elections$year <- substr(governor_elections$RaceDate, 1, 4)
governor_elections$gov_rep_votes <- as.numeric(as.vector(governor_elections$RepVotes))
governor_elections$gov_dem_votes <- as.numeric(as.vector(governor_elections$DemVotes))
governor_elections$OtherVotes <- as.numeric(as.vector(governor_elections$OtherVotes))
governor_elections$ThirdVotes <- as.numeric(as.vector(governor_elections$ThirdVotes))
governor_elections$gov_dem <- governor_elections$gov_dem_votes/(governor_elections$gov_dem_votes+governor_elections$gov_rep_votes)
governor_elections$ThirdVotes[is.na(governor_elections$ThirdVotes)]  <-  0
governor_elections$gov_dem_votes[is.na(governor_elections$gov_dem_votes)]  <-  0
governor_elections$gov_rep_votes[is.na(governor_elections$gov_rep_votes)]  <-  0
governor_elections$OtherVotes[is.na(governor_elections$OtherVotes)]  <-  0
governor_elections$county_name <- paste(tolower(governor_elections$Area),tolower(governor_elections$AreaType), sep=" ")
governor_elections$county_name <- gsub(" county", "", governor_elections$county_name)
governor_elections$county_name <- gsub(" city city", " city", governor_elections$county_name)
governor_elections$county_name <- paste(tolower(governor_elections$county_name), sep=" ")
governor_elections$county_name <- gsub(" county", "", governor_elections$county_name)
governor_elections$county_name <- gsub(" parish", "", governor_elections$county_name)
governor_elections$gov_total_votes <- governor_elections$gov_rep_votes +
	governor_elections$gov_dem_votes +
	governor_elections$ThirdVotes +
	governor_elections$OtherVotes
governor_elections <- dplyr::select(governor_elections, year, county_name, State, gov_rep_votes, gov_dem_votes, gov_dem,gov_total_votes)
governor_elections <- subset(governor_elections, year>1990 | year<1990 )
governor_elections <- subset(governor_elections, year!=1988 )

if(include_licensed==0){
	senate_elections <- read.csv(file="Election_Returns/senate_public.csv")
}
if(include_licensed==1){
	senate_elections <- read.csv(file="Election_Returns/senate.csv")
}
senate_elections$year <- substr(senate_elections$RaceDate, 1, 4)
senate_elections$sen_rep_votes <- as.numeric(as.vector(senate_elections$RepVotes))
senate_elections$sen_dem_votes <- as.numeric(as.vector(senate_elections$DemVotes))
senate_elections$OtherVotes <- as.numeric(as.vector(senate_elections$OtherVotes))
senate_elections$ThirdVotes <- as.numeric(as.vector(senate_elections$ThirdVotes))
senate_elections$sen_dem <- senate_elections$sen_dem_votes/(senate_elections$sen_dem_votes+senate_elections$sen_rep_votes)
senate_elections$county_name <- paste(tolower(senate_elections$Area),tolower(senate_elections$AreaType), sep=" ")
senate_elections$county_name <- gsub(" county", "", senate_elections$county_name)
senate_elections$county_name <- gsub(" city city", " city", senate_elections$county_name)
senate_elections$county_name <- paste(tolower(senate_elections$county_name), sep=" ")
senate_elections$county_name <- gsub(" county", "", senate_elections$county_name)
senate_elections$county_name <- gsub(" parish", "", senate_elections$county_name)
senate_elections$ThirdVotes[is.na(senate_elections$ThirdVotes)]  <-  0
senate_elections$sen_dem_votes[is.na(senate_elections$sen_dem_votes)]  <-  0
senate_elections$sen_rep_votes[is.na(senate_elections$sen_rep_votes)]  <-  0
senate_elections$OtherVotes[is.na(senate_elections$OtherVotes)]  <-  0
senate_elections$sen_total_votes <- senate_elections$sen_rep_votes +
	senate_elections$sen_dem_votes +
	senate_elections$ThirdVotes +
	senate_elections$OtherVotes
senate_elections <- dplyr::select(senate_elections, year, county_name, State, sen_rep_votes, sen_dem_votes, sen_dem,sen_total_votes)
senate_elections_mn_dfl <- subset(senate_elections, (year==1990 | year==1972 | year==1970)  & State=="Minnesota")
senate_elections_mn_dfl <- rename(senate_elections_mn_dfl,
	sen_rep_votes_dfl=sen_rep_votes,
	sen_dem_votes_dfl=sen_dem_votes, 
	sen_dem_dfl=sen_dem,
	sen_total_votes_dfl=sen_total_votes)
	
senate_elections <- subset(senate_elections, year>1990)

elections_recent <- merge(president_elections, governor_elections, by=c("State", "county_name", "year"), all.x=T, all.y=T)
elections_recent <- merge(elections_recent, senate_elections, by=c("State", "county_name", "year"), all.x=T, all.y=T)

elections_recent$year <- as.numeric(as.vector(elections_recent$year))


elections_returns$state_abb <- "NA"
elections_returns$state_abb <- as.vector(elections_returns$state_abb)
elections_returns2 <- full_join(elections_returns, elections_recent)
elections_returns2$state_abb <- as.vector(elections_returns2$state_abb)
elections_returns2 <- full_join(elections_returns2, elections_2016)
elections_returns2 <- full_join(elections_returns2, elections_2018)
elections_returns2$state_abb <- as.vector(elections_returns2$state_abb)

table(elections_returns2$year)

s <- read.csv(paste("StateCodes.csv",sep=""))
s <- dplyr::select(s, state_fips, Name, POAbrv, ICPSRCode)

elections_returns2 <- merge(elections_returns2, s, by.x="icpsr_state", by.y="ICPSRCode",all.x=T)
elections_returns2 <- merge(elections_returns2, s, by.x="State", by.y="Name",all.x=T)
elections_returns2 <- merge(elections_returns2, s, by.x="state_abb", by.y="POAbrv",all.x=T)
elections_returns2$state_abb <- as.vector(elections_returns2$state_abb)
elections_returns2$POAbrv.x <- as.vector(elections_returns2$POAbrv.x)
elections_returns2$POAbrv.y <- as.vector(elections_returns2$POAbrv.y)
elections_returns2$state_abb[elections_returns2$state_abb=="NA" | is.na(elections_returns2$state_abb)] <- elections_returns2$POAbrv.x[elections_returns2$state_abb=="NA" | is.na(elections_returns2$state_abb)]
elections_returns2$state_abb[elections_returns2$state_abb=="NA" | is.na(elections_returns2$state_abb)] <- elections_returns2$POAbrv.y[elections_returns2$state_abb=="NA" | is.na(elections_returns2$state_abb)]
elections_returns2$state_fips <- as.vector(elections_returns2$state_fips)
elections_returns2$state_fips[is.na(elections_returns2$state_fips)] <- elections_returns2$state_fips.x[is.na(elections_returns2$state_fips)]
elections_returns2$state_fips[is.na(elections_returns2$state_fips)] <- elections_returns2$state_fips.y[is.na(elections_returns2$state_fips)]
elections_returns2$Name <- as.vector(elections_returns2$Name.x)
elections_returns2$Name[is.na(elections_returns2$Name)] <- as.vector(elections_returns2$Name.y[is.na(elections_returns2$Name)])

elections_returns2$county_name <- tolower(elections_returns2$county_name)
elections_returns2 <- merge(elections_returns2, senate_elections_mn_dfl, by.x=c("Name", "county_name", "year"), by.y=c("State", "county_name", "year"), all.x=T, all.y=T)
elections_returns2$sen_rep_votes[!is.na(elections_returns2$sen_rep_votes_dfl)] <- elections_returns2$sen_rep_votes_dfl[!is.na(elections_returns2$sen_rep_votes_dfl)]
elections_returns2$sen_dem_votes[!is.na(elections_returns2$sen_dem_votes_dfl)] <- elections_returns2$sen_dem_votes_dfl[!is.na(elections_returns2$sen_dem_votes_dfl)]
elections_returns2$sen_dem[!is.na(elections_returns2$sen_dem_dfl)] <- elections_returns2$sen_dem_dfl[!is.na(elections_returns2$sen_dem_dfl)]
elections_returns2$sen_total_votes[!is.na(elections_returns2$sen_total_votes_dfl)] <- elections_returns2$sen_total_votes_dfl[!is.na(elections_returns2$sen_total_votes_dfl)]


elections_returns2 <- dplyr::select(elections_returns2, -state_fips.x, -POAbrv.x, -state_fips.y, -POAbrv.y, -Name.x, -Name.y)
elections_returns2[elections_returns2==""] <- NA
table(elections_returns2$state_abb,elections_returns2$year)
table(elections_returns2$state_fips,elections_returns2$year)

## Elections data from OurCampaigns.com:
downballot_elecs_oc_processed <- read.dta("Election_Returns/downballot_elecs_oc_processed.dta")
downballot_elecs_oc_processed$county <- tolower(downballot_elecs_oc_processed$county)
elections_returns2$Name[is.na(elections_returns2$Name)] <- as.vector(elections_returns2$State[is.na(elections_returns2$Name)])

elections_returns3 <- merge(elections_returns2,downballot_elecs_oc_processed,by.x=c("Name", "year", "county_name"), by.y=c("state", "year", "county"), all.x=T) 
elections_returns3$stateoffice_dem[is.na(elections_returns3$stateoffice_dem)] <- elections_returns3$downballot_demshare[is.na(elections_returns3$stateoffice_dem)]

## State legislative elections from Carl Klarner:
sld_1968_1989 <- read.dta("Election_Returns/sld_1968_1989.dta")
sld_1968_1989$County_Name <- tolower(sld_1968_1989$County_Name)
sld_1968_1989 <- dplyr::select(sld_1968_1989, ICPSR_State, Election_Year, County_Name, dem_votes, rep_votes, sld_demshare)
sld_1968_1989 <- dplyr::rename(sld_1968_1989, sld_dem_votes=dem_votes)
sld_1968_1989 <- dplyr::rename(sld_1968_1989, sld_rep_votes=rep_votes)
sld_1968_1989 <- dplyr::rename(sld_1968_1989, year=Election_Year)

elections_returns3 <- merge(elections_returns3,sld_1968_1989,by.x=c("Name", "year", "county_name"), by.y=c("ICPSR_State", "year", "County_Name"), all.x=T) 


## make some county names in ICPSR data compatiable with fips codes
elections_returns3$county_name  <-  gsub(".*/", "", elections_returns3$county_name)

elections_returns3$county_name[elections_returns3$county_name=="benton" & elections_returns3$state_abb=="AL"& elections_returns3$year<1992] <- "calhoun"
elections_returns3$county_name[elections_returns3$county_name=="baker" & elections_returns3$state_abb=="AL"& elections_returns3$year<1992] <- "chilton"
elections_returns3$county_name[elections_returns3$county_name=="sanford" & elections_returns3$state_abb=="AL"& elections_returns3$year<1992] <- "lamar"
elections_returns3$county_name[elections_returns3$county_name=="cotaco" & elections_returns3$state_abb=="AL"& elections_returns3$year<1992] <- "morgan"
elections_returns3$county_name[elections_returns3$county_name=="st clair" & elections_returns3$state_abb=="AL"& elections_returns3$year<1992] <- "st. clair"
elections_returns3$county_name[elections_returns3$county_name=="hancock" & elections_returns3$state_abb=="AL"& elections_returns3$year<1992] <- "winston"
elections_returns3$county_name[elections_returns3$county_name=="st francis" & elections_returns3$state_abb=="AR"& elections_returns3$year<1992] <- "st. francis"

elections_returns3$county_name[elections_returns3$county_name=="la salle" & elections_returns3$state_abb=="IL"& elections_returns3$year<1992] <- "lasalle"
elections_returns3$county_name[elections_returns3$county_name=="de witt" & elections_returns3$state_abb=="TX"& elections_returns3$year<1992] <- "dewitt"
elections_returns3$county_name[elections_returns3$county_name=="richland " & elections_returns3$state_abb=="SC"& elections_returns3$year<1992] <- "richland"
elections_returns3$county_name[elections_returns3$county_name=="greenville " & elections_returns3$state_abb=="SC"& elections_returns3$year<1992] <- "greenville"
elections_returns3$county_name[elections_returns3$county_name=="carson" & elections_returns3$state_abb=="NV"& elections_returns3$year<1992] <- "carson city"
elections_returns3$county_name[elections_returns3$county_name=="la moure" & elections_returns3$state_abb=="ND"& elections_returns3$year<1992] <- "lamoure"
elections_returns3$county_name[elections_returns3$county_name=="james" & elections_returns3$state_abb=="VA"& elections_returns3$year<1992] <- "james city"
elections_returns3$county_name[elections_returns3$county_name=="charles" & elections_returns3$state_abb=="VA"& elections_returns3$year<1992] <- "charles city"

elections_returns3$county_name[elections_returns3$county_name=="delores" & elections_returns3$state_abb=="CO"& elections_returns3$year<1992] <- "dolores"
elections_returns3$county_name[elections_returns3$county_name=="d.c." & elections_returns3$state_abb=="DC"& elections_returns3$year<1992] <- "washington"

elections_returns3$county_name[elections_returns3$county_name=="benton" & elections_returns3$state_abb=="FL"& elections_returns3$year<1992] <- "hernando"
elections_returns3$county_name[elections_returns3$county_name=="cass" & elections_returns3$state_abb=="GA"& elections_returns3$year<1992] <- "bartow"
elections_returns3$county_name[elections_returns3$county_name=="oahu" & elections_returns3$state_abb=="HI"& elections_returns3$year<1992] <- "honolulu"
elections_returns3$county_name[elections_returns3$county_name=="buncombe" & elections_returns3$state_abb=="IA"& elections_returns3$year<1992] <- "lyon"
elections_returns3$county_name[elections_returns3$county_name=="lagrange" & elections_returns3$state_abb=="IN"] <- "la grange"

elections_returns3$county_name[elections_returns3$county_name=="o'brien" & elections_returns3$state_abb=="IA"] <- "o brien"

elections_returns3$county_name[elections_returns3$county_name=="lasalle" & elections_returns3$state_abb=="IL"& elections_returns3$year<1992] <- "la salle"

elections_returns3$county_name[elections_returns3$county_name=="dorn" & elections_returns3$state_abb=="KS"& elections_returns3$year<1992] <- "neosho"
elections_returns3$county_name[elections_returns3$county_name=="lykins" & elections_returns3$state_abb=="KS"& elections_returns3$year<1992] <- "miami"

elections_returns3$county_name[elections_returns3$county_name=="lasalle" & elections_returns3$state_abb=="LA"& elections_returns3$year<1992] <- "la salle"
elections_returns3$county_name[elections_returns3$county_name=="st john the bapti" & elections_returns3$state_abb=="LA"& elections_returns3$year<1992] <- "st john the baptist"
elections_returns3$county_name[elections_returns3$county_name=="vermillion" & elections_returns3$state_abb=="LA"& elections_returns3$year<1992] <- "vermilion"

elections_returns3$county_name[elections_returns3$county_name=="pembina" & elections_returns3$state_abb=="MN"& elections_returns3$year<1992] <- "kittson"
elections_returns3$county_name[elections_returns3$county_name=="toombs" & elections_returns3$state_abb=="MN"& elections_returns3$year<1992] <- "wilkin"

elections_returns3$county_name[elections_returns3$county_name=="rives" & elections_returns3$state_abb=="MO"& elections_returns3$year<1992] <- "henry"
elections_returns3$county_name[elections_returns3$county_name=="st genevieve" & elections_returns3$state_abb=="MO"& elections_returns3$year<1992] <- "ste genevieve"
elections_returns3$county_name[elections_returns3$county_name=="van buren" & elections_returns3$state_abb=="MO"& elections_returns3$year<1992] <- "carter"

elections_returns3$county_name[elections_returns3$county_name=="lamoure" & elections_returns3$state_abb=="ND"& elections_returns3$year<1992] <- "la moure"
elections_returns3$county_name[elections_returns3$county_name=="johnston" & elections_returns3$state_abb=="NE"& elections_returns3$year<1992] <- "saline"
elections_returns3$county_name[elections_returns3$county_name=="l'eau qui co" & elections_returns3$state_abb=="NE"& elections_returns3$year<1992] <- "knox"

elections_returns3$county_name[elections_returns3$county_name=="buchanan" & elections_returns3$state_abb=="TX"& elections_returns3$year<1992] <- "stephens"
elections_returns3$county_name[elections_returns3$county_name=="davis" & elections_returns3$state_abb=="TX"& elections_returns3$year<1992] <- "jeff davis"
elections_returns3$county_name[elections_returns3$county_name=="dewitt" & elections_returns3$state_abb=="TX"& elections_returns3$year<1992] <- "de witt"

elections_returns3$county_name[elections_returns3$county_name=="bad ax" & elections_returns3$state_abb=="WI"& elections_returns3$year<1992] <- "vernon"
elections_returns3$county_name[elections_returns3$county_name=="dallas" & elections_returns3$state_abb=="WI"& elections_returns3$year<1992] <- "barron"
elections_returns3$county_name[elections_returns3$county_name=="la point" & elections_returns3$state_abb=="WI"& elections_returns3$year<1992] <- "bayfield"
elections_returns3$county_name[elections_returns3$county_name=="dekalb"] <- "de kalb"

elections_returns3$county_name[elections_returns3$county_name=="miami-dade"& elections_returns3$state_abb=="FL"] <- "dade"
elections_returns3$county_name[elections_returns3$county_name=="desoto"& elections_returns3$state_abb=="FL"] <- "de soto"


va_city_names_icma <- read.csv(file="va_city_names_icma.csv")
va_city_names_icma$county_name2 <- as.vector(va_city_names_icma$county_name2)
elections_returns3 <- merge(elections_returns3, va_city_names_icma, by.x=c("state_abb", "county_name"), by.y=c("state_abb", "city_icma"),all.x=T)
elections_returns3$county_name[!is.na(elections_returns3$county_name2)] <- elections_returns3$county_name2[!is.na(elections_returns3$county_name2)]

elections_returns3$county_name <- trimws(elections_returns3$county_name, "r")

#elections_returns3$county_name <- gsub(" city","", elections_returns3$county_name)
elections_returns3$county_name <- gsub(" county","", elections_returns3$county_name)
elections_returns3$fips <- as.numeric(as.vector(elections_returns3$fips))
county_fips <- read.csv("counties_fips.csv")
county_fips$county_name <- tolower(county_fips$county_name)
elections_returns3$county_name <- gsub("st. ", "st ", elections_returns3$county_name)
elections_returns4 <- merge(elections_returns3, county_fips, by=c("state_abb", "county_name"), all.x=T)
elections_returns4$fips[is.na(elections_returns4$fips)] <- elections_returns4$fips2[is.na(elections_returns4$fips)]
## name change for Dade County, FL
elections_returns4$fips[elections_returns4$fips==12025] <-  12086
write.dta(elections_returns4, file="elections_returns3.dta")

table(elections_returns4$year[is.na(elections_returns4$fips)])

table(elections_returns4$year[!is.na(elections_returns4$fips)])
table(elections_returns3$year[!is.na(elections_returns3$state_abb)])

## Add Dave Leip House data
years <- c(1992, 1994, 1996, 1998, 2000, 2002, 2004, 2006, 2008, 2010, 2012, 2014)
merge_leip <- 0
if(merge_leip==1){
leip_house <- NA
for(i in 1:length(years)){
	file = paste("Election_Returns/Leip_House_Election_Data_1992-2014/House_Election_Data_", years[i],".xlsx", sep="")
		data  <-   openxlsx::read.xlsx(xlsxFile=file, sheet="County")
		colnames(data)[which(colnames(data)=="Democratic")][2] <- "Democratic_Votes"
		colnames(data)[which(colnames(data)=="Republican")][2] <- "Republican_Votes"
	data$year <- years[i]
	data  <-  data[ , !duplicated(colnames(data))]
	data <- dplyr::select(data, X1, X2, Total.Vote, Democratic_Votes,Republican_Votes, FIPS, year,LSAD_TRANS)
	leip_house <- rbind(leip_house, data)
	print(years[i])
}

leip_house <- rename(leip_house,house_dem_votes=Democratic_Votes)
leip_house <- rename(leip_house,house_rep_votes=Republican_Votes)
leip_house <- rename(leip_house,house_total_votes=Total.Vote)
leip_house <- mutate(leip_house,house_dem=house_dem_votes/(house_dem_votes+house_rep_votes))
leip_house <- rename(leip_house,fips=FIPS)
leip_house <- rename(leip_house,county_name=X1)
leip_house <- rename(leip_house,state_abb=X2)
write.dta(leip_house,file="Election_Returns/leip_house.dta")	
}

if (include_licensed==0){
	leip_house <- read.dta(,file="Election_Returns/leip_house_public.dta")	
}
if (include_licensed==1){
	leip_house <- read.dta(,file="Election_Returns/leip_house.dta")	
}

data <- read.dta(file="elections_returns3.dta")

data$county_name[data$county_name=="prince georges" & data$state_abb=="MD"] <- "prince george's"
data$county_name[data$county_name=="queen annes" & data$state_abb=="MD"] <- "queen anne's"
data$county_name[data$county_name=="st. marys" & data$state_abb=="MD"] <- "st. mary's"

data$county_name[data$county_name=="du page" & data$state_abb=="IL"] <- "dupage"
data$county_name[data$county_name=="la salle" & data$state_abb=="IL"] <- "lasalle"
data$county_name[data$county_name=="la porte" & data$state_abb=="IN"] <- "laporte"
data$county_name[data$county_name=="de soto" & data$state_abb=="MS"] <- "desoto"
data$county_name[data$county_name=="la moure" & data$state_abb=="ND"] <- "lamoure"
data$county_name[data$county_name=="de witt" & data$state_abb=="TX"] <- "dewitt"
data$county_name[data$county_name=="greenville " & data$state_abb=="SC"] <- "greenville"
data$county_name[data$county_name=="carson" & data$state_abb=="NV"] <- "carson city"
data$county_name[data$county_name=="richland " & data$state_abb=="SC"] <- "richland"

data$county_name <- tolower(data$county_name)
leip_house$county_name <- tolower(leip_house$county_name)
leip_house <- subset(leip_house, state_abb!="AK")
leip_house <- subset(leip_house, state_abb!="T")
leip_house <- subset(leip_house, state_abb!="PR")

leip_house <- select(leip_house, -state_abb, -county_name)
data <- merge(data, leip_house, by=c("fips", "year"), all.x=T, all.y=T)

data$house_dem_votes.x[is.na(data$house_dem_votes.x)] <- data$house_dem_votes.y[is.na(data$house_dem_votes.x)]
data$house_rep_votes.x[is.na(data$house_rep_votes.x)] <- data$house_rep_votes.y[is.na(data$house_rep_votes.x)]
data$house_total_votes.x[is.na(data$house_total_votes.x)] <- data$house_total_votes.y[is.na(data$house_total_votes.x)]
data$house_dem.x[is.na(data$house_dem.x)] <- data$house_dem.y[is.na(data$house_dem.x)]

data <- rename(data,house_dem_votes=house_dem_votes.x)
data <- rename(data,house_rep_votes=house_rep_votes.x)
data <- rename(data,house_total_votes=house_total_votes.x)
data <- rename(data,house_dem=house_dem.x)
data <- dplyr::select(data, -house_dem_votes.y)
data <- dplyr::select(data, -house_rep_votes.y)
data <- dplyr::select(data, -house_total_votes.y)
data <- dplyr::select(data, -house_dem.y)

data <- select(data, -Name)
data <- select(data, -State)

data <- arrange(data, year, state_abb, fips)
data <- filter(data, !is.na(fips) & fips!=0)
validation <- 0
if (validation==1){
	HealyLenz2016 <- read.dta("Election_Returns/HealyLenz2016/QCEW_data.dta")
	HealyLenz2016 <- dplyr::select(HealyLenz2016, fips,dem_pct2, year)
	data$fips <- as.numeric(as.vector(data$fips))
	data$year <- as.numeric(as.vector(data$year))
	data <- full_join(data, HealyLenz2016)
	as.data.frame(group_by(subset(data, !is.na(pres_dem) & !is.na(dem_pct2)), year, state_abb) %>%
		summarise(
		cors=cor(pres_dem, dem_pct2, use="complete.obs")
	))
}	

data <- dplyr::select(data, fips,state_abb, county_name, year, pres_dem, house_dem, sen_dem, gov_dem, 
				stateoffice_dem, downballot_demshare,sld_demshare, sen_dem_votes, 
				sen_rep_votes, sen_total_votes,
 				 pres_dem_votes, pres_rep_votes, pres_total_votes,
 				 house_dem_votes, house_rep_votes, house_total_votes,
 				 gov_dem_votes, gov_rep_votes, gov_total_votes, 
 				 stateoffice_dem,
 				 sld_dem_votes, sld_rep_votes,
 				 pres_dem, sen_dem, gov_dem, house_dem)
write.dta(data,file="elections_returns3.dta")	
